﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Models;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    /// <summary>
    /// 评价
    /// </summary>
   
   public class AppraiseDAL
    {
        //评价添加
        public static int appraiseAdd(Appraise a)
        {
            string sql = "insert into tb_Appraise(FoodID,UserID,Appurl,Content,Time,Score,Rider,Ordertitle,Anonymous) values(@FoodID,@UserID,@Appurl,@Content,@Time,@Score,@Rider,@Ordertitle,@Anonymous)";
            SqlParameter[] para = {
            new SqlParameter("@FoodID",a.FoodID),
            new SqlParameter("@UserID",a.UserID),
            new SqlParameter("@Appurl",a.Appurl),
            new SqlParameter("@Content",a.Content),
            new SqlParameter("@Time",a.Time),
            new SqlParameter("@Score",a.Score),
            new SqlParameter("@Rider",a.Rider),
            new SqlParameter("@Ordertitle",a.Ordertitle),
            new SqlParameter("@Anonymous",a.Anonymous)
            };
            return SqlDBHelper.ExecuteNonQuery(sql, para);
        }
        //评价判重
        public static DataTable appraisePC(int foodid,int userid,string bh) {
            string sql = "select * from tb_Appraise where FoodID="+ foodid + " and UserID=" + userid + " and Ordertitle="+ bh;
            return SqlDBHelper.GetDataTable(sql);
        }
        //前台评价查看
        public static DataTable appraiseCK(int foodid)
        {
            string sql = "select a.Content,a.Time,u.UserName,a.Anonymous from tb_Appraise a,tb_User u where a.UserID=u.UserID and FoodID=" + foodid;
            return SqlDBHelper.GetDataTable(sql);
        }
        //判断这个美食是否有评价
        public static DataTable sfPJ(int foodid)
        {
            string sql = "select * from tb_Appraise where FoodID=" + foodid;
            return SqlDBHelper.GetDataTable(sql);
        }
        //查询此商品被评论了多次次
        public static object num(int foodid)
        {
            string sql = "select COUNT(*) from tb_Appraise where FoodID=" + foodid;
            return SqlDBHelper.ExecuteScalar(sql);
        }
        //后台评论查看刷新
        public static DataTable backstagePLCK()
        {
            string sql = @"select a.AppraiseID,f.FoodName,u.UserName,a.Content,a.Appurl,a.Time,a.Score,a.Rider
                            from tb_Appraise a,tb_User u, tb_Food f
                             where a.UserID = u.UserID and a.FoodID = f.FoodID";
            return SqlDBHelper.GetDataTable(sql);
        }
        //查询评论条数
        public static object backstageNum()
        {
            string sql = "select COUNT(*) from tb_Appraise";
            return SqlDBHelper.ExecuteScalar(sql);
        }
        //后台评论查询
        public static DataTable mhcx(string fname,string uname)
        {
            string sql = @"select a.AppraiseID,f.FoodName,u.UserName,a.Content,a.Appurl,a.Time,a.Score,a.Rider
                    from tb_Appraise a,tb_User u,tb_Food f
                    where a.UserID=u.UserID and a.FoodID=f.FoodID and f.FoodName like'%"+ fname + "%' and u.UserName like'%"+uname+"%'";
            return SqlDBHelper.GetDataTable(sql);
        }
        //评论删除
        public static int plSC(int id)
        {
            string sql = "delete from tb_Appraise where AppraiseID=" + id;
            return SqlDBHelper.ExecuteNonQuery(sql);
        }
        //个人首页评论查看刷新
        public static DataTable backstagePLCK(int id)
        {
            string sql = @"select a.AppraiseID,f.FoodName,u.UserName,a.Content,a.Appurl,a.Time,a.Score,a.Rider
                            from tb_Appraise a,tb_User u, tb_Food f
                             where a.UserID = u.UserID and a.FoodID = f.FoodID and u.UserID="+id;
            return SqlDBHelper.GetDataTable(sql);
        }
    }
}
